google-spreadsheet

What is google-spreadsheet?

The google-spreadsheet npm package allows you to interact with Google Sheets through the Google Sheets API. It provides a simple and intuitive way to read, write, and manage Google Sheets data programmatically.

What are google-spreadsheet's main functionalities?

Authentication

This feature allows you to authenticate with the Google Sheets API using either service account credentials or OAuth2 tokens.

const { GoogleSpreadsheet } = require('google-spreadsheet');
const doc = new GoogleSpreadsheet('<spreadsheet-id>');

// Using service account credentials
await doc.useServiceAccountAuth(require('./path/to/credentials.json'));

// OR using OAuth2 tokens
await doc.useOAuth2Client(oAuth2Client);

Loading a Spreadsheet

This feature allows you to load the spreadsheet's metadata and worksheets, making it possible to interact with the spreadsheet's structure and data.

await doc.loadInfo(); // loads document properties and worksheets
console.log(doc.title);

Reading Rows

This feature allows you to read rows from a specific sheet within the spreadsheet. You can access individual cell values and iterate through the rows.

const sheet = doc.sheetsByIndex[0]; // or use doc.sheetsById[id]
const rows = await sheet.getRows(); // can pass in { limit, offset }
console.log(rows[0].name);

Adding Rows

This feature allows you to add new rows to a sheet within the spreadsheet. You can specify the values for each column in the new row.

await sheet.addRow({ name: 'John Doe', email: '[e-mail]' });

Updating Cells

This feature allows you to update the values of specific cells in a row and save the changes back to the spreadsheet.

const row = rows[0];
row.name = 'Jane Doe';
await row.save();

Other packages similar to google-spreadsheet

googleapis

The googleapis package is a comprehensive library for interacting with various Google APIs, including the Google Sheets API. It provides more extensive functionality beyond just Google Sheets, but it requires more setup and configuration compared to google-spreadsheet.

gsheets

The gsheets package is a lightweight library for accessing Google Sheets data. It offers basic functionalities for reading and writing data but lacks some of the advanced features and ease of use provided by google-spreadsheet.

g-sheets-api

The g-sheets-api package is designed for simple and quick access to Google Sheets data. It is less feature-rich compared to google-spreadsheet but can be a good choice for straightforward use cases where minimal setup is desired.

README

google-spreadsheet

The most popular Google Sheets API wrapper for javascript

• multiple auth options - service account (w/ optional impersonation), OAuth 2.0, API key (read-only)
• cell-based API - read, write, bulk-updates, formatting
• row-based API - read, update, delete (based on the old v3 row-based calls)
• managing worksheets - add, remove, resize, change title, formatting

Docs site - Full docs available at https://theoephraim.github.io/node-google-spreadsheet

🚨 Google Deprecation Warning - affects older version (v2) of this module 🚨

Google is phasing out their old v3 api, which the older version of this module used. Originally they were going to shut it down on March 3rd 2020, but have pushed that date back to June 2021.

Regardless, please upgrade to the latest version of this module (v3) which uses the newer sheets v4 API

---

🌈 Installation - npm i google-spreadsheet --save or yarn add google-spreadsheet

Examples

the following examples are meant to give you an idea of just some of the things you can do

IMPORTANT NOTE - To keep the examples concise, I'm calling await at the top level which is not allowed by default in most versions of node. If you need to call await in a script at the root level, you must instead wrap it in an async function like so:

(async function() {
  await someAsyncFunction();
}());

The Basics

const { GoogleSpreadsheet } = require('google-spreadsheet');

// Initialize the sheet - doc ID is the long id in the sheets URL
const doc = new GoogleSpreadsheet('<the sheet ID from the url>');

// Initialize Auth - see https://theoephraim.github.io/node-google-spreadsheet/#/getting-started/authentication
await doc.useServiceAccountAuth({
  // env var values are copied from service account credentials generated by google
  // see "Authentication" section in docs for more info
  client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
  private_key: process.env.GOOGLE_PRIVATE_KEY,
});

await doc.loadInfo(); // loads document properties and worksheets
console.log(doc.title);
await doc.updateProperties({ title: 'renamed doc' });

const sheet = doc.sheetsByIndex[0]; // or use doc.sheetsById[id] or doc.sheetsByTitle[title]
console.log(sheet.title);
console.log(sheet.rowCount);

// adding / removing sheets
const newSheet = await doc.addSheet({ title: 'hot new sheet!' });
await newSheet.delete();

More info:

• GoogleSpreadsheet
• GoogleSpreadsheetWorksheet
• Authentication

Working with rows

// create a sheet and set the header row
const sheet = await doc.addSheet({ headerValues: ['name', 'email'] });

// append rows
const larryRow = await sheet.addRow({ name: 'Larry Page', email: '[e-mail]' });
const moreRows = await sheet.addRows([
  { name: 'Sergey Brin', email: '[e-mail]' },
  { name: 'Eric Schmidt', email: '[e-mail]' },
]);

// read rows
const rows = await sheet.getRows(); // can pass in { limit, offset }

// read/write row values
console.log(rows[0].name); // 'Larry Page'
rows[1].email = '[e-mail]'; // update a value
await rows[1].save(); // save updates
await rows[1].delete(); // delete a row

More info:

• GoogleSpreadsheetWorksheet > Working With Rows
• GoogleSpreadsheetRow

Working with cells

await sheet.loadCells('A1:E10'); // loads range of cells into local cache - DOES NOT RETURN THE CELLS
console.log(sheet.cellStats); // total cells, loaded, how many non-empty
const a1 = sheet.getCell(0, 0); // access cells using a zero-based index
const c6 = sheet.getCellByA1('C6'); // or A1 style notation
// access everything about the cell
console.log(a1.value);
console.log(a1.formula);
console.log(a1.formattedValue);
// update the cell contents and formatting
a1.value = 123.456;
c6.formula = '=A1';
a1.textFormat = { bold: true };
c6.note = 'This is a note!';
await sheet.saveUpdatedCells(); // save all updates in one call

More info:

• GoogleSpreadsheetWorksheet > Working With Cells
• GoogleSpreadsheetCell

Why?

This module provides an intuitive wrapper around Google's API to simplify common interactions

While Google's v4 sheets api is much easier to use than v3 was, the official googleapis npm module is a giant meta-tool that handles every Google product. The module and the API itself are awkward and the docs are pretty terrible, at least to get started.

In what situation should you use Google's API directly?
This module makes trade-offs for simplicity of the interface. Google's API provides a mechanism to make many requests in parallel, so if speed and efficiency is extremely important to your use case, you may want to use their API directly. There are also several features of their API that are not implemented here yet.

Support & Contributions

This module was written and is actively maintained by Theo Ephraim.

Are you actively using this module for a commercial project? Want to help support it?
Buy Theo a beer

Sponsors

None yet - get in touch!

Contributing

Contributions are welcome, but please follow the existing conventions, use the linter, add relevant tests, add relevant documentation.

The docs site is generated using docsify. To preview and run locally so you can make edits, run npm run docs:preview and head to http://localhost:3000 The content lives in markdown files in the docs folder.

License

This is free and unencumbered public domain software. For more info, see https://unlicense.org.

Changelog

v3.3.0

13 May 2022

• add sheet.duplicate, clean up dependencies, docus updates 4cec425
• bump/audit dependencies 42d918b
• add export methods 07b0783
• clearRows and clear(range) 5bfa235
• clarify docs around loading cells c1d2f23